/*============================================================= */
/*                          TABLAS                              */
/*============================================================= */

/*============================================================= */
/* Table: Profesor                                              */
/*============================================================= */
create table Profesor  (
   id_Cedula                  INT                 not null,
   nombreProfesor             varchar(100)               not null,
   apellidoProfesor           varchar(100)               not null,
   crnProfesor                INT                 not null,
   emailProfesor              varchar(100)               not null,
   claveProfesor              varchar(100)               not null,
   fechaNac                   DATE                        not null,
   sexoProfesor               varchar(2)                 not null,
   telefonoProfesor           varchar(100)               not null,
   contratoProfesor           varchar(100)               not null,
   estadoProfesor             varchar(100)               not null,
   constraint CHECK_SEXO_PROFESOR check (sexoProfesor in ('M','F')),
   constraint CHECK_CONTRATO_PROFESOR check (contratoProfesor in ('Completo','Parcial')), 
   constraint CHECK_ESTADO_PROFESOR check (estadoProfesor in ('Activo','Inactivo')),
   constraint PK_PROFESOR primary key (id_Cedula)
);

/*============================================================= */
/* Table: Salon                                                 */
/*============================================================= */
CREATE TABLE IF NOT EXISTS `salon` (
  `id_salon` int(10) NOT NULL AUTO_INCREMENT,
  `tipo` varchar(20) NOT NULL,
  `nombre` varchar(20) NOT NULL,
  `edificio` varchar(40) NOT NULL,
  `capacidad` int(100) NOT NULL,
  `status` varchar(20) NOT NULL,
  PRIMARY KEY (`id_salon`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

/*============================================================= */
/* Table: Universidad                                           */
/*============================================================= */
create table Universidad
(
           id_Universidad	int		primary key,
           nombre		varchar(100)    not null,
           correo               varchar(50)     not null,
           clave                varchar(30)     not null
);


/*============================================================= */
/* Table: Escuela                                               */
/*============================================================= */
create table Escuela (
id_Escuela		int				not null,
fk_Universidad	int				not null,
nombre			varchar(30)		not null,
descripcion		varchar(700)	null,
telefono		varchar(15)		null,
estado			int				not null,
loguin			varchar(20)		not null,
password		varchar(20)		not null,
constraint pk_escuela primary key (id_Escuela)
);

/*============================================================= */
/* Table: Pensum                                                */
/*============================================================= */
CREATE TABLE Pensums
(
    id_Pensum        int         primary key,
    fechaInicio      date        not null,
    fechaFin         date        null,
    statusPensum     varchar(30) not null,
    urlPensum        varchar(30) not null,
    fk_id_escuela    int         not null
		
);

/*============================================================= */
/* Table: Estudiante                                            */
/*============================================================= */
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE  TABLE [dbo].[Estudiante] (
  [id_estudiante] [int] IDENTITY(1,1) NOT NULL ,
  [noExpediente] [varchar](50) NOT NULL ,
  [nombre] [varchar](50) NOT NULL ,
  [apellido] [varchar](50) NOT NULL ,
  [fechaNac] [date] NOT NULL ,
  [sexo] [varchar](50) NOT NULL ,
  [telefono] [varchar](50) NOT NULL ,
  [correo] [varchar](50) NOT NULL ,
  [fk_id_pensum] [int] NULL ,
   CONSTRAINT [PK_ESTUDIANTE] PRIMARY KEY CLUSTERED 
 (
	[id_estudiante] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

/*============================================================= */
/* Table: HorarioPreMatricula                                   */
/*============================================================= */
CREATE  TABLE HorarioPreMatricula
(
	id_HorarioPreMatricula int NOT NULL ,
	status varchar(10) NOT NULL ,
	fecha date NOT NULL ,
	fk_Estudiante int NULL ,
	constraint PK_HORARIO_PREMATRICULA primary key (id_HorarioPreMatricula),
	constraint CHECK_STATUS_HORARIO check (status in ('Activo','Pendiente','Historial'))
);

/*============================================================= */
/* Table: Materia                                               */
/*============================================================= */
create table Materia  (
   id_Materia                 INT                  not null,
   nombreMateria              varchar(100)               not null,
   horasTeoria                INT                   not null,
   horasPractica              INT                   not null,
   descripcion                varchar(600)               not null,
   statusMateria              varchar(20)                not null,
   constraint CHECK_ESTADO_MATERIA check (statusMateria in ('Activo','Inactivo')),
   constraint PK_MATERIA primary key (id_Materia)
);

/*============================================================= */
/* Table: Seccion_Materia                                       */
/*============================================================= */
create table SeccionMateria  (
   id_Seccion                INT                   not null,
   periodoAcademico          varchar(100)                not null,
   numeroSeccion             INT                   not null,
   fk_Profesor               INT                   not null,
   fk_Materia                INT                  not null,
   constraint PK_SECCION_MATERIA primary key (id_Seccion)
);

/*============================================================= */
/* Table:Practica                                               */
/*============================================================= */
CREATE TABLE Practica (
id_Practica INT NOT NULL,
numeroSeccion INT NOT NULL ,
nombrePractica VARCHAR(50) NOT NULL ,
fk_SeccionMAteria INT NOT NULL
);

/*============================================================= */
/* Table: Evaluacion                                            */
/*============================================================= */
CREATE TABLE EVALUACION
(	
	ID_EVALUACION INT PRIMARY KEY,
	tipoEvaluacion VARCHAR(100) NOT NULL,
	nombreEvaluacion VARCHAR(100) NOT NULL,
	porcentajeEvaluacion INT NOT NULL,
	fechaTentativaEvaluacion DATETIME,
	fk_id_seccion INT
);

/*============================================================= */
/* Table: EstudianteHorario                                     */
/*============================================================= */
CREATE  TABLE EstudianteHorario
(
  id_EstudianteHorario int IDENTITY(1,1) NOT NULL ,
  fk_HorarioPreMatricula int NULL ,
  fk_Practica int NULL ,
  fk_SeccionMateria int NULL ,
  constraint PK_ESTUDIANTE_HORARIO primary key (id_EstudianteHorario)
);

/*============================================================= */
/* Table: Listado                                               */
/*============================================================= */
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE  TABLE [dbo].[Listado] (
  [fk_id_estudiante][int] NOT NULL,
  [fk_id_seccion][int] NOT NULL,
   CONSTRAINT [PK_LISTADO] PRIMARY KEY CLUSTERED 
 (
	[fk_id_estudiante],
	[fk_id_seccion]
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

/*============================================================= */
/* Table: EstudianteMateria                                     */
/*============================================================= */
create table SeccionEstudiante  (
   fk_Seccion                INT                     not null,
   fk_Estudiante             INT                     not null,
   constraint PK_LISTA_ESTU primary key (fk_Seccion, fk_Estudiante)
);


/*============================================================= */
/* Table: Nota                                                  */
/*============================================================= */
create table Nota
(
           id_Nota			int			not null,
           notaObtenida		int        	not null,
           fk_Estudiante	int         not null,
           fk_Evaluacion	int			not null,
           
           constraint pk_Nota primary key (id_Nota)
);

/*============================================================= */
/* Table: ArchivoPdf                                            */
/*============================================================= */
create table ArchivoPdf
(
           id_ArchivoPdf	int		primary key,
           nombre		varchar(100)    not null,
           ruta               	varchar(200)    not null,
	   descripcion		varchar(100)	null,
	   fk_Profesor		int		not null,
           fk_SeccionMateria   	int		not null
);

/*============================================================= */
/* Table: HorarioClase                                          */
/*============================================================= */
create table HorarioClase
(
   id_Horario                 INT                      not null,
   diaSemana                 varchar(10)          not null,
   horaInicio                  INT                   not null,
   horaFin                      INT                   not null,
   fk_Salon                      INT                      not null,
   fk_SeccionMateria        INT,
   fk_SeccionPractica        INT,
   constraint CHECK_DIA_CLASE check (diaSemana in ('Lunes','Martes','Miercoles','Jueves','Viernes','Sabado','Domingo')),
   constraint PK_HORARIO_CLASE primary key (id_Horario)
);

/*============================================================= */
/* Table: EscuelaProfesor                                                       */
/*============================================================= */
create table EscuelaProfesor (
id_EscuelaProfesor		int		not null,
fk_Escuela				int		not null,
fk_Profesor				int		not null,
fechaInicio				date	not null,
fechaFin				date	null,
cargo					varchar(20)	not null,
constraint pk_escuela_profesor primary key (id_EscuelaProfesor)
);
go

/*============================================================= */
/* Table: PensumMateria                                         */
/*============================================================= */
CREATE TABLE PensumsMaterias
( 
	 semestre              int         not null,
	 numCreditos           int         not null,
	 fk_id_materia         int         not null,
	 fk_id_materia_prela   int         not null,
	 fk_id_pensum          int         not null,
	 
	 constraint pk_id_materia_pensum_prela primary key(fk_id_materia,fk_id_materia_prela,fk_id_pensum)
);

/*============================================================= */
/* Table: EscuelaEstudiante                                     */
/*============================================================= */
create table EscuelaEstudiante (
id_EscuelaEstudiante		int		not null,
fk_Escuela					int		not null,
fk_Estudiante				int		not null,
fechaInicio					date	not null,
fechaFin					date	null,
constraint pk_escuela_estudiante primary key (id_EscuelaEstudiante)
);

/*============================================================= */
/* Table: Rol                                                   */
/*============================================================= */
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE  TABLE [dbo].[ROL] (
  [id_rol] [int] IDENTITY(1,1) NOT NULL ,
  [correo] [varchar](50) NOT NULL ,
  [pass] [varchar](50) NOT NULL ,
  [fk_id_estudiante] [int] NULL, 
  [fk_id_profesor] [int] NULL,
  [fk_id_escuela] [int] NULL,
  [fk_id_universidad] [int] NULL,
   CONSTRAINT [PK_ROL] PRIMARY KEY CLUSTERED 
 (
	[id_rol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



/*============================================================= */
/*                          ALTERS                              */
/*============================================================= */

/* Tabla EstudianteHorario */

ALTER TABLE EstudianteHorario  WITH CHECK
	ADD  CONSTRAINT FK_EH_HP] FOREIGN KEY(fk_HorarioPreMatricula)
		REFERENCES HorarioPreMatricula (id_HorarioPreMatricula);
ALTER TABLE EstudainteHorario CHECK CONSTRAINT FK_EH_HP;

ALTER TABLE EstudianteHorario  WITH CHECK 
	ADD CONSTRAINT FK_EH_P] FOREIGN KEY(fk_Practica)
		REFERENCES Practica (id_Practica);
ALTER TABLE [dbo].[EstudainteHorario] CHECK CONSTRAINT FK_EH_P;

ALTER TABLE EstudianteHorario WITH CHECK 
	ADD CONSTRAINT FK_EH_SM FOREIGN KEY(fk_SeccionMateria)
		REFERENCES SeccionMateria (id_SeccionMateria);
ALTER TABLE EstudainteHorario CHECK CONSTRAINT FK_EH_SM;


/* Tabla HorarioPreMatricula */

ALTER TABLE HorarioPreMatricula WITH CHECK 
	ADD CONSTRAINT [FK_E_HP] FOREIGN KEY(fk_Estudiante)
		REFERENCES Estudiante (id_Estudiante);
ALTER TABLE HorarioPreMatricula CHECK CONSTRAINT FK_E_HP;

/* Tabla ESTUDIANTE */

ALTER TABLE [dbo].[Estudiante]  WITH CHECK ADD  CONSTRAINT [FK_EstudiantePensum] FOREIGN KEY([fk_id_pensum])
REFERENCES [dbo].[Pensum] ([id_pensum])
GO
ALTER TABLE [dbo].[Estudiante] CHECK CONSTRAINT [FK_EstudiantePensum]
GO

/* Tabla ROL */

ALTER TABLE [dbo].[ROL]  WITH CHECK ADD  CONSTRAINT [FK_Rol_Estudiante] FOREIGN KEY([fk_id_estudiante])
REFERENCES [dbo].[ESTUDIANTE] ([id_estudiante])
GO
ALTER TABLE [dbo].[ROL] CHECK CONSTRAINT [FK_Rol_Estudiante]
GO

ALTER TABLE [dbo].[ROL]  WITH CHECK ADD  CONSTRAINT [FK_Rol_Profesor] FOREIGN KEY([fk_id_profesor])
REFERENCES [dbo].[PROFESOR] ([id_cedula])
GO
ALTER TABLE [dbo].[ROL] CHECK CONSTRAINT [FK_Rol_Profesor]
GO

ALTER TABLE [dbo].[ROL]  WITH CHECK ADD  CONSTRAINT [FK_Rol_Escuela] FOREIGN KEY([fk_id_escuela])
REFERENCES [dbo].[ESCUELA] ([id_escuela])
GO
ALTER TABLE [dbo].[ROL] CHECK CONSTRAINT [FK_Rol_Escuela]
GO

ALTER TABLE [dbo].[ROL]  WITH CHECK ADD  CONSTRAINT [FK_Rol_Universidad] FOREIGN KEY([fk_id_universidad])
REFERENCES [dbo].[UNIVERSIDAD] ([id_Universidad])
GO
ALTER TABLE [dbo].[ROL] CHECK CONSTRAINT [FK_Rol_Universidad]
GO


/* Tabla Listado */

ALTER TABLE [dbo].[Listado]  WITH CHECK ADD  CONSTRAINT [FK_LISTADO_ESTUDIANTE] FOREIGN KEY([fk_id_estudiante])
REFERENCES [dbo].[Estudiante] ([id_estudiante])
GO
ALTER TABLE [dbo].[Listado] CHECK CONSTRAINT [FK_LISTADO_ESTUDIANTE]
GO

ALTER TABLE [dbo].[Listado]  WITH CHECK ADD  CONSTRAINT [FK_LISTADO_SECCION] FOREIGN KEY([fk_id_seccion])
REFERENCES [dbo].[SeccionMateria] ([id_seccion])
GO
ALTER TABLE [dbo].[Listado] CHECK CONSTRAINT [FK_LISTADO_SECCION]
GO

/* Tabla ArchivoPdf */

alter table ArchivoPdf add constraint fk_Profesor  foreign key(fk_Profesor) references Profesor(id_Profesor) on delete cascade;

alter table ArchivoPdf add constraint fk_SeccionMateria foreign key(fk_SeccionMateria) references SeccionMateria(id_SeccionMateria) on delete cascade;

/* Tabla SeccionMateria */

alter table SeccionMateria add constraint FK_SECCION_MATERIA_PROFESOR foreign key (fk_Profesor) references Profesor (id_Cedula);

alter table SeccionMateria add constraint FK_SECCION_MATERIA_MATERIA foreign key (fk_Materia) references Materia (id_Materia);

/* Tabla SeccionEstudiante */

alter table SeccionEstudiante add constraint FK_LISTA_ESTU_SECCION foreign key (fk_Seccion) references SeccionMateria (id_Seccion);

alter table SeccionEstudiante add constraint FK_LISTA_ESTU_ESTUDIANTE foreign key (fk_Estudiante) references Estudiante (id_estudiante);

/* Tabla Pensum */

ALTER TABLE Pensums ADD CONSTRAINT fk_pensums1 FOREIGN KEY(fk_id_escuela)REFERENCES Escuelas(id_escuela)

/* Tabla PensumMateria */

ALTER TABLE PensumsMaterias ADD CONSTRAINT fk_pensumMaterias1 FOREIGN KEY(fk_id_pensum)REFERENCES Pensum(id_pensum)


ALTER TABLE PensumsMaterias ADD CONSTRAINT fk_pensumsMaterias2 FOREIGN KEY(fk_id_materia)REFERENCES Materia(id_materia)


ALTER TABLE PensumsMaterias ADD CONSTRAINT fk_pensumsMaterias3 FOREIGN KEY(fk_id_materia_prela)REFERENCES Materia(id_materia)

/* Tabla Escuela */

alter table Escuela add constraint fk_escuela_universidad foreign key (fk_Universidad) references Universidad(id_Universidad);

/* Tabla EscuelaEstudiante */

alter table EscuelaEstudiante add constraint fk_Escuela foreign key(fk_Escuela) references Escuela(id_Escuela);
alter table EscuelaEstudiante add constraint fk_Estudiante foreign key(fk_Estudiante) references estudiante(id_Estudiante);

/* Tabla EscuelaProfesor */

alter table EscuelaProfesor add constraint ck_cargo check (cargo in ('tiempo completo','tiempo parcial','director'));
alter table EscuelaProfesor add constraint fk_Escuela_1 foreign key(fk_Escuela) references Escuela(id_Escuela);
alter table EscuelaProfesor add constraint fk_Profesor_2 foreign key(fk_Profesor) references Profesor(id_Profesor);

/* Tabla Practica */

ALTER TABLE Practica ADD CONSTRAINT id_Practica PRIMARY KEY (id_Practica);
ALTER TABLE Practica ADD CONSTRAINT fk_PracticaSeccion FOREIGN KEY (fk_SeccionMateria) REFERENCES SeccionMateria (id_SeccionMateria);

/* Tabla Nota */

alter table Nota add constraint fk_Estudiante foreign key(fk_Estudiante) references Estudiante(id_Estudiante);
alter table Nota add constraint fk_Evaluacion foreign key(fk_Evaluacion) references Evaluacion(id_Evaluacion);
